﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spBkp_SnapshotExists]
#-- Purpose:		Determines if a snapshot of a database exists.  Purpose is 
#--					to be used in a night batch process to determine if a 
#--					snapshot exists to restore from.
#--	Last Update:	04/23/2013
#--					For a complete history - please review comments in Version
#--					Control.
#-- Required XPs:	None
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spBkp_SnapshotExists]
(
	@database_name			sysname,
	@label					sysname
)
AS

SET NOCOUNT ON 

--- Validate Parameters
IF ISNULL(@database_name, '') = '' OR @label IS NULL
  BEGIN
	RAISERROR ('All parameters are required to create a backup snapshot', 16, 1)
	RETURN
  END
IF NOT EXISTS (SELECT TOP 1 * FROM [master].[sys].[databases] WHERE name = @database_name)
  BEGIN
	RAISERROR ('The database you requested a snapshot of does not exist', 16, 1)
	RETURN
  END

--- Declare Local Variables
DECLARE		@snap_db_name sysname, @sSQL nvarchar(MAX), @file_name sysname, @directory_name nvarchar(MAX),
			@i int, @snap_exists bit

--- Initialize Local Variables
SELECT		@database_name = LTRIM(RTRIM(@database_name)),
			@label = LTRIM(RTRIM(@label)),
			@snap_db_name = 'SNAP_' + @database_name + '_' + @label,
			@snap_exists = 0

--- Determine is the snapshot exists
IF EXISTS (SELECT TOP 1 * FROM [master].[sys].[databases] WHERE source_database_id IS NOT NULL AND name = @snap_db_name)
	SET	@snap_exists = 1

--- Return the answer
RETURN @snap_exists